﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data;
using MySql;
using MySql.Data.MySqlClient;
using System.Data;
using MMOArpgServer.Pool;

namespace MMOArpgServer.DAL
{
    public class DBConnect
    {
        private MySqlCommand _MySqlCommand;
        private MySqlConnection _MySqlConnection;
        private string _MySqlConnectStr = "Database=mmoarpg_account;Data Source=127.0.0.1;User Id=root;Password=root;pooling=false;CharSet=utf8;port=3306";
        private StringBuilder _SQLCommandStr = new StringBuilder();
        private MySqlDataReader _DataReader;
        public DBConnect()
        {

        }
        public void ConnectDB()
        {
            _MySqlConnection = new MySqlConnection(_MySqlConnectStr);
            try
            {
                _MySqlConnection.Open();

            }
            catch (MySqlException e)
            {
                Console.WriteLine(e);
            }
        }
        /// <summary>
        /// 关闭当前连接并回收到连接池中。
        /// </summary>
        public void CloseConnect()
        {
            _MySqlConnection.Close();
            DBConnectPool.GetInstance().CloseConnect(this);
        }
        /// <summary>
        /// 服务器关闭时调用关闭
        /// </summary>
        public void Close()
        {
            _DataReader.Close();
            _MySqlConnection.Close();
        }

        #region 查询数据
        public MySqlDataReader Select(string tableName, MySqlParameter[] parameters)
        {
            CloseReader();
            _SQLCommandStr.Clear();
            _SQLCommandStr.Append("select * from " + tableName + " where " + parameters[0].ParameterName + "=?" + parameters[0].ParameterName);
            for (int i = 1; i < parameters.Length; i++)
            {
                _SQLCommandStr.Append(" and " + parameters[i].ParameterName + "=?" + parameters[i].ParameterName);
            }
            _MySqlCommand = new MySqlCommand(_SQLCommandStr.ToString(), _MySqlConnection);
            _MySqlCommand.Parameters.AddRange(parameters);
            _DataReader = _MySqlCommand.ExecuteReader();
            return _DataReader;
        }

        private void CloseReader()
        {
            if (_DataReader!=null) {
                _DataReader.Close();
            }
        }

        public MySqlDataReader Select(string tableName, MySqlParameter parameter)
        {
            return Select(tableName, new MySqlParameter[] { parameter });
        }

        #endregion

        #region 插入数据
        /// <summary>
        /// 将数据插入到数据库
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="parameters">希望插入的参数组</param>
        /// <returns>resultCode</returns>
        /// 
        public int Insert(string tableName, MySqlParameter[] parameters)
        {

            _SQLCommandStr.Clear();

            _SQLCommandStr.Append("insert into  " + tableName + " (" + parameters[0].ParameterName);
            for (int i = 1; i < parameters.Length; i++)
            {
                _SQLCommandStr.Append("," + parameters[i].ParameterName);
            }
            _SQLCommandStr.Append(")");

            _SQLCommandStr.Append(" values (?" + parameters[0].ParameterName);
            for (int i = 1; i < parameters.Length; i++)
            {
                _SQLCommandStr.Append(",?" + parameters[i]);
            }
            _SQLCommandStr.Append(")");
            _MySqlCommand = new MySqlCommand(_SQLCommandStr.ToString(), _MySqlConnection);
            _MySqlCommand.Parameters.AddRange(parameters);
            return _MySqlCommand.ExecuteNonQuery();
        }
        /// <summary>
        /// 如果主键不冲突，将数据插入到数据库
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="parameters">希望插入的参数组</param>
        /// <param name="mainKey">主键</param>
        /// <returns>resultCode</returns>
        public int Insert(string tableName, MySqlParameter[] parameters, MySqlParameter mainKey = null)
        {
            if (mainKey != null)
            {
                _DataReader = Select(tableName, new MySqlParameter[] { mainKey });
                if (_DataReader.Read())
                {
                    _DataReader.Close();
                    return -1;
                }
                _DataReader.Close();
            }
            return Insert(tableName, parameters);
        }
        public int Insert(string tableName, MySqlParameter parameter, MySqlParameter mainKey = null)
        {
            return Insert(tableName, new MySqlParameter[] { parameter }, mainKey);
        }

        #endregion

        #region 更新数据
        public int Update(string tableName, MySqlParameter parameter, MySqlParameter mainKey)
        {
      
            return Update(tableName, new MySqlParameter[] { parameter }, mainKey);
        }
        /// <summary>
        /// 更新数据的说。
        /// </summary>
        /// <param name="tableName">表明</param>
        /// <param name="parameters">更新的数据</param>
        /// <param name="mainKey">主键</param>
        /// <returns></returns>
        public int Update(string tableName, MySqlParameter[] parameters, MySqlParameter mainKey)
        {
            _SQLCommandStr.Clear();
            CloseReader();
            _SQLCommandStr.Append("Update " + tableName + " Set "+ parameters[0].ParameterName+"=?"+ parameters[0].ParameterName);
            for (int i = 1; i < parameters.Length; i++)
            {
                _SQLCommandStr.Append(", "+parameters[i].ParameterName + "=?" + parameters[i].ParameterName);

            }

            _SQLCommandStr.Append(" where " + mainKey.ParameterName + "=?" + mainKey.ParameterName);
            try
            {
                _MySqlCommand = new MySqlCommand(_SQLCommandStr.ToString(), _MySqlConnection);
                _MySqlCommand.Parameters.AddRange(parameters);
                _MySqlCommand.Parameters.Add(mainKey);
            }
            catch (Exception e) {
                Console.WriteLine(e.Message);
            }
            return _MySqlCommand.ExecuteNonQuery();
        }
        #endregion 更新数据

        #region 删除数据
        /// <summary>
        /// 删除数据的说。
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="mainKey"></param>
        /// <returns></returns>
        public int Delete(string tableName, MySqlParameter mainKey)
        {
            _SQLCommandStr.Clear();
            CloseReader();
            _SQLCommandStr.Append("delete from " + tableName + " where " + mainKey.ParameterName + "=?" + mainKey.ParameterName);
            _MySqlCommand = new MySqlCommand(_SQLCommandStr.ToString(), _MySqlConnection);
            _MySqlCommand.Parameters.Add(mainKey);
            return _MySqlCommand.ExecuteNonQuery();
        }
        #endregion 
    }
}
